package com.yc.fruitmarket.biz.impl;

import java.util.List;
import java.util.Map;

import com.yc.fruitmarket.biz.productBiz;
import com.yc.fruitmarket.dao.DBHelper;

public class productBizImpl implements productBiz {
	private DBHelper db = new DBHelper();
		//添加商品
		@Override
		public int add(Map<String, String> map, String path) {
			String sql = "select tid from type where tname = ?";
			List<Map<String, String>> list = db.find(sql, map.get("type"));
			String tid = list.get(0).get("tid");
			System.out.println(path);
			//添加
			String sql1 = "insert into product values(null,?,?,?,?,?,?,?,?,1,?)";
			int result = db.doUpdate(sql1, map.get("pname"),map.get("intprice"),map.get("outprice"),map.get("inventory"),map.get("brand"),tid,map.get("weight"),map.get("des"),path);
			return result;
		}

	//条件查询商品
		@Override
		public List<Map<String, String>> getProduct(int max, int min, int page, int pageSize, String pname, String tids) {
			String sql = "select product.pid, pname,intprice,outprice,inventory,brand,product.tid,weight,des,pic,count(pval.pvid) as count,  AVG(pval.starnum) as pf from "
					+ " product LEFT JOIN pval on pval.pid = product.pid" ;
			if(!"".equals(tids) && tids != null) {
				sql += " LEFT JOIN type  ON product.tid = type.tid ";
			}
			sql += " where product.status = 1";
			if(!"".equals(tids) && tids != null) {
				sql += " and type.tid in ("+ tids +")";
			}
			if(!"".equals(pname)) {
				sql += " and pname like '%"+pname+"%'";
			}
			if(min != 0 && max != 0) {
				sql += " and outprice >= " + min + " and outprice <= "+ max;
			}else if(min == 0 && max != 0) {
				sql += " and outprice <= "+max;
			}else if(min != 0 && max == 0) {
				sql += " and outprice >= "+min;
			}
			sql += " GROUP BY product.pid, pname,intprice,outprice,inventory,brand,product.tid,weight,des,pic";
			sql += " limit ?,?";
			return db.find(sql,(page - 1)*pageSize, pageSize);
		}
	//查询商品数量
		@Override
		public int getProductCount(int max, int min, String pname, String tids) {
			String sql = "select count(product.pid) as count from product ";
			if(!"".equals(tids) && tids != null) {
				sql += " LEFT JOIN type  ON product.tid = type.tid ";
			}
			
			sql += " where product.status = 1 ";
			if(!"".equals(tids) && tids != null) {
				sql += " and type.tid in ("+ tids +")";
			}
			
			if(!"".equals(pname)) {
				sql += " and pname like '%"+pname+"%'";
			}
			if(min != 0 && max != 0) {
				sql += " and outprice >= " + min + " and outprice <= "+ max;
			}else if(min == 0 && max != 0) {
				sql += " and outprice <= "+max;
			}else if(min != 0 && max == 0) {
				sql += " and outprice >= "+min;
			}
			List<Map<String, String>> list = db.find(sql);
			return Integer.parseInt(list.get(0).get("count"));
		}
		@Override
		public List<Map<String, String>> searchProduct(String pid) {
			String sql = "select product.pid,type.tname, pname,intprice,outprice,inventory,brand,product.tid,weight,des,pic,count(pval.pvid) as count,  AVG(pval.starnum) as pf from  product LEFT JOIN pval on pval.pid = product.pid "
					+" LEFT JOIN type  ON product.tid = type.tid where product.status = 1 and product.pid = ? GROUP BY product.pid,type.tname, pname,intprice,outprice,inventory,brand,product.tid,weight,des,pic";
			List<Map<String, String>> list = db.find(sql, pid);
			return list;
		}
		
		

		
		//查询所有的商品
		@Override
		public List<Map<String, String>> getAllGoods() {
			String sql = "select * from product,type where product.tid = type.tid";
			return db.find(sql);
		}
		
		//条件查询
		@Override
		public List<Map<String, String>> bygoods(String pid, String pname, String inventory, String intprice, String outprice, String brand, String tname, String weight, String des, String status, String currentPage4, String pagesize) {
			String sql = "select * from product, type where product.tid = type.tid";
			if(!"".equals(pid) && pid != null) {
				sql += " and pid = " + pid;
			}
			if(!"".equals(pname) && pname != null) {
				sql += " and pname like '%"+pname+"%'";
			}
			if(!"".equals(inventory) && inventory != null) {
				sql += " and inventory = " + inventory;
			}
			if(!"".equals(intprice) && intprice != null) {
				sql += " and intprice = " + intprice;
			}
			if(!"".equals(outprice) && outprice != null) {
				sql += " and outprice = " + outprice;
			}
			if(!"".equals(brand) && brand != null) {
				sql += " and brand like '%"+brand+"%'";
			}
			if(!"".equals(tname) && tname != null) {
				sql += " and tname like '%"+tname+"%'";
			}
			if(!"".equals(weight) && weight != null) {
				sql += " and weight like '%"+weight+"%'";
			}
			if(!"".equals(des) && des != null) {
				sql += " and des like '%"+des+"%'";
			}
			if(!"".equals(status) && status != "") {
				if(!"-1".equals(status)) {
					sql += " and product.status = " + status;
				}
			}
			List<Map<String, String>> list = db.find(sql);
			String a = String.valueOf(list.size());
			 sql += " limit ?,?";
			 List<Map<String, String>> list1 = db.find(sql, (Integer.parseInt(currentPage4)-1) * Integer.parseInt(pagesize), Integer.parseInt(pagesize));
			 for(Map<String, String> map : list1) {
					map.put("count", a);
				}
			return list1;
		}
		
		//修改商品
		@Override
		public int updateL(Map<String, String> map, String path) {
			//通过tname查询tid
			String sql1 = "select tid from type where tname = ?";
			List<Map<String, String>> list = db.find(sql1, map.get("type"));
			String tid = list.get(0).get("tid");
			if("".equals(path)) {
				String sql = "update product set pname = ?, intprice = ?, outprice = ?, inventory = ?, brand = ?, tid = ?, weight = ?, des = ? where pid = ?";
				return db.doUpdate(sql, map.get("pname"), map.get("intprice"), map.get("outprice"), map.get("inventory"), map.get("brand"), tid, map.get("weight"), map.get("des"), map.get("pid"));
			}else {
				String sql = "update product set pname = ?, intprice = ?, outprice = ?, inventory = ?, brand = ?, tid = ?, weight = ?, des = ?, pic = ? where pid = ?";
				return db.doUpdate(sql, map.get("pname"), map.get("intprice"), map.get("outprice"), map.get("inventory"), map.get("brand"), tid, map.get("weight"), map.get("des"), path, map.get("pid"));
			}
		}

		@Override
		public List<Map<String, String>> getAboutProduct(String pid) {
			String sql1 = "select tid from product where pid = ?";
			List<Map<String, String>> list = db.find(sql1, pid);
			int tid = Integer.parseInt(list.get(0).get("tid"));
			String sql = "select product.pid, pname,intprice,outprice,inventory,brand,product.tid,weight,des,pic,count(pval.pvid) as count,  AVG(pval.starnum) "+
		"as pf from product LEFT JOIN pval on pval.pid = product.pid LEFT JOIN type  ON product.tid = type.tid where product.status = 1 and "+
					"type.tid = ? and product.pid != ? "+
		"GROUP BY product.pid, pname,intprice,outprice,inventory,brand,product.tid,weight,des,pic ORDER BY product.inventory desc LIMIT 0,4";
			return db.find(sql, tid, pid);
		}
		
		//上架，下架商品
		@Override
		public int banL(String pid, String status) {
			String sql = "";
			if("0".equals(status)) {
				sql = "update product set status = 1 where pid = " + pid;
			}
			if("1".equals(status)) {
				sql = "update product set status = 0 where pid = " + pid;
			}
			return db.doUpdate(sql);
		}

		@Override
		public List<Map<String, String>> getnewproduct() {
			String sql = "select product.pid, pname,intprice,outprice,inventory,brand,product.tid,weight,des,pic,count(pval.pvid) as count,  AVG(pval.starnum) as pf from " + 
						" product LEFT JOIN pval on pval.pid = product.pid "+"  where product.status = 1 "+
						"GROUP BY product.pid, pname,intprice,outprice,inventory,brand,product.tid,weight,des,pic ORDER BY product.pid desc limit 0,4";
			
			List<Map<String, String>> list = db.find(sql);
			if(list.size() > 0) {
				return list;
			}else {
				return null;
			}
		}

		@Override
		public List<Map<String, String>> getbuyproduct() {
			String findpid = "SELECT product.pid ,pname ,SUM(order_detail.num) AS total_sales FROM order_detail  "+
							"JOIN product  ON order_detail.pid = product.pid  GROUP BY  product.pid ,pname  ORDER BY total_sales DESC LIMIT 4";
			List<Map<String, String>> list = db.find(findpid);
			String pid = "";
			for(int i = 0; i < list.size(); i++) {
				pid += list.get(i).get("pid")+",";
			}
			// 移除最后一个逗号，并添加 "pids"  
			if (!pid.isEmpty()) {  
			    pid = pid.substring(0, pid.length() - 1);  
			}
			
			String sql = "select product.pid, pname,intprice,outprice,inventory,brand,product.tid,weight,des,pic,count(pval.pvid) as count,  AVG(pval.starnum) as pf from "
					+ " product LEFT JOIN pval on pval.pid = product.pid where product.status = 1 and product.pid in ("+ pid +") "+
					"GROUP BY product.pid, pname,intprice,outprice,inventory,brand,product.tid,weight,des,pic ORDER BY product.pid desc ";
			
			return db.find(sql);
		}
		
		//获取所有的商品名称
		@Override
		public List<Map<String, String>> allpname() {
			String sql = "select pname from product";
			return db.find(sql);
		}
		
		//统计商品数量
		@Override
		public List<Map<String, String>> getproductnumL() {
			String sql = "SELECT   \r\n"
					+ "    COUNT(*) AS a,  \r\n"
					+ "    SUM(CASE WHEN product.status = 1 THEN 1 ELSE 0 END) AS b,  \r\n"
					+ "    SUM(CASE WHEN product.status = 0 THEN 1 ELSE 0 END) AS c,\r\n"
					+ "		SUM(CASE WHEN product.inventory = 0 THEN 1 ELSE 0 END) AS d\r\n"
					+ "FROM   \r\n"
					+ "    product;";
			return db.find(sql);
		}

}
